const utils = require("../utils/index.js");
const pool = require("../pool.js");
const poolmomo = require("../poolmomo.js");

const moment = require("moment-timezone");

// 产品ID到试用天数的映射
const PRODUCT_TRIAL_DAYS_MAP = {
    'weekly_plan_trail': 3, //全能
    'roku_one_week_trial': 3, //roku
    'roku_year_one_trial': 3, //roku

    'beauty.basic.weekly.v2': 3, //beauty
    'beauty.basic.weekly.v4': 3, //beauty
    'beauty.basic.yearly.v2': 7, //beauty
    'beauty.basic2.weekly.v6': 3, //beauty
    'beauty.discount.basic.yearly.v3': 3, //beauty
    'beauty.discount.basic.yearly.v4': 7, //beauty

    'mypulse.ft.basic.weekly.v1': 3, //心率
    'mypulse.ft.basic.weekly.v2': 3, //心率
    'mypulse.discount.basic.yearly.v2': 3, //心率
    'mypulse.discount.basic.yearly.v4': 3, //心率
    'mypulse.ft.basic.weekly.v4': 3, //心率


    'weekly_trial': 3, //qr

    ' ': 3, //wa
    'monthly_premium_with_trial': 7, //wa
    'yearly_premium': 7, //wa

    'UniversalTVRemoteWeekly': 3, //全能2
    'weekly.v3': 3, //全能2

    'nova.discount.year.v1': 3, //note
    'nova.discount.week.v2': 3, //note
    'nova.discount.week.v3': 3, //note
    'nova.discount.week.v4': 3, //note


    'monthly_trial': 3, //momo
    'yearly_trial': 7, //momo
    'quarterly_trial2': 3, //momo

    //首周优惠
    'mypulse.basic.weekly.v2':7,//xinlv
    'weekly_first_week_offer':7,//qr
    'beauty.basic.weekly.v7':7,//beauty
    'weekly_premium_with_1week_offer':7,//wa
    'Monthly_Premium_with_1Month_Offer':31,//wa
    'weekly.v2':7,//全能2
    'nova.discount.week.v1':7,//note
};
const DEFAULT_TRIAL_DAYS = 3; // 没在上面声明的产品，走默认3天


function getPool(pk) {
    // 指定pk走poolmomo，其它pk默认走pool
    return pk == 'com.trans.translationAiAndSubtitle' ? poolmomo : pool;
}

async function mysqlQueryWithPk(pk, sql, arr) {
    return new Promise((resolve, reject) => {
        getPool(pk).query(sql, arr, (error, results) => {
            if (error) return reject(error);
            resolve(results);
        });
    });
}
class AppleSubscriptionStatsService {
    constructor() { }

    /**
     * 执行每日基础统计任务（不包含转正率）
     * @param {string} pk 包名
     * @param {string} targetDate 统计日期 YYYY-MM-DD，不传则为昨天
     */
    async executeDailyBasicStats(pk, targetDate = null) {
        // 没有指定日期则默认统计昨天
        if (!targetDate) {
            targetDate = moment.tz("Asia/Shanghai").subtract(1, "days").format("YYYY-MM-DD");
        }
        console.log(`开始统计 ${pk} 在 ${targetDate} 的基础数据`);

        // 获取该包下所有产品ID
        const productIds = await mysqlQueryWithPk(pk,
            `SELECT DISTINCT product_id 
             FROM js_app_apple_notifications 
             WHERE pk = ? AND environment = 'Production' AND product_id IS NOT NULL`,
            [pk]
        );

        for (const {
            product_id
        }
            of productIds) {
            await this.calculateBasicProductStats(pk, product_id, targetDate);
        }
        console.log(`完成统计 ${pk} 在 ${targetDate} 的基础数据`);
    }
    /**
     * 查询某天一次性买断人数
     * @param {*} pk 
     * @param {*} productId 
     * @param {*} statDate 
     * @returns 
     */
    async getOneTimePurchaseUsers(pk, productId, statDate) {
        const result = await mysqlQueryWithPk(pk,
            `SELECT COUNT(*) as count
            FROM js_app_apple_notifications
            WHERE pk = ? AND product_id = ? AND environment = 'Production'
                AND DATE(signed_date) = ?
                AND notification_type = 'ONE_TIME_CHARGE'`,
            [pk, productId, statDate]
        );
        return result[0]?.count || 0;
    }

    /**
     * 计算单个产品的基础统计数据（不包含转正率）
     */
    async calculateBasicProductStats(pk, productId, statDate) {
        try {
            // 1. 成功续订人数
            const successfulRenewals = await this.getSuccessfulRenewals(pk, productId, statDate);
            // 2. 成功退款人数
            const successfulRefunds = await this.getSuccessfulRefunds(pk, productId, statDate);
            // 3. 试用用户数
            const trialUsers = await this.getTrialUsers(pk, productId, statDate);
            // 4. 无试用无首周优惠直接购买用户数
            const directPurchaseUsers = await this.getDirectPurchaseUsers(pk, productId, statDate);

            // 加：一次性买断人数
            const oneTimePurchaseUsers = await this.getOneTimePurchaseUsers(pk, productId, statDate);
            //恢复订阅人数
            const resubscribeCount = await this.getresubscribeCount(pk, productId, statDate);
            // // 5. 初始化试用转正跟踪表（为后续转正率统计做准备）
            await this.initTrialConversionTracking(pk, productId, statDate);

            // 6. 插入/更新基础统计（转正率字段设为NULL或0）
            await this.upsertBasicDailyStats({
                pk,
                productId,
                statDate,
                successfulRenewals,
                successfulRefunds,
                trialUsers,
                directPurchaseUsers,
                oneTimePurchaseUsers,
                resubscribeCount
            });
            console.log(`完成产品 ${productId} 的基础统计`);
        } catch (error) {
            console.error(`产品 ${productId} 基础统计失败:`, error);
            throw error;
        }
    }

    /**
     * 执行转正率统计任务
     * @param {string} pk 包名
     * @param {string} targetDate 目标日期，统计这一天开始试用且已过试用期的数据
     */
    // async executeConversionRateStats(pk, targetDate = null) {
    //     // 如果没有指定日期，则统计试用期满的日期
    //     if (!targetDate) {
    //         // 使用北京时间计算
    //         const todayBJ = moment.tz("Asia/Shanghai");
    //         const targetDateBJ = todayBJ.subtract(DEFAULT_TRIAL_DAYS + 1, "days");
    //         targetDate = targetDateBJ.format("YYYY-MM-DD");

    //         console.log(`当前北京时间: ${moment.tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss")}`);
    //         console.log(`计算的目标统计日期: ${targetDate} (试用开始日期，已过${DEFAULT_TRIAL_DAYS}天试用期)`);
    //     }

    //     console.log(`开始统计 ${pk} 在 ${targetDate} 开始试用的转正率数据`);

    //     // 获取该包下所有产品ID
    //     const productIds = await mysqlQueryWithPk(pk,
    //         `SELECT DISTINCT product_id 
    //      FROM js_app_apple_notifications 
    //      WHERE pk = ? AND environment = 'Production' AND product_id IS NOT NULL`,
    //         [pk]
    //     );
    //     console.log(productIds,'productIds')
    //     for (const {
    //         product_id
    //     }
    //         of productIds) {
    //         await this.calculateConversionRate(pk, product_id, targetDate);
    //     }
    //     console.log(`完成统计 ${pk} 在 ${targetDate} 的转正率数据`);
    // }
    async executeConversionRateStats(pk, targetDate = null) {
        console.log(`开始统计 ${pk} 的转正率数据`);

        // 获取该包下所有产品ID
        const productIds = await mysqlQueryWithPk(pk,
            `SELECT DISTINCT product_id 
             FROM js_app_apple_notifications 
             WHERE pk = ? AND environment = 'Production' AND product_id IS NOT NULL`,
            [pk]
        );

        console.log(productIds, 'productIds');

        for (const { product_id } of productIds) {
            // 如果没有指定统一的目标日期，则为每个产品分别计算其试用开始日期
            let productTargetDate = targetDate;

            if (!targetDate) {
                // 获取该产品的试用天数
                let trialDays = PRODUCT_TRIAL_DAYS_MAP[product_id] || DEFAULT_TRIAL_DAYS;
                if (pk == 'com.seamobitech.QRCodeReadAndGenerate' && product_id == 'yearly_trial') {
                    trialDays = 3;
                }

                // 基于产品的试用天数计算目标日期
                const todayBJ = moment.tz("Asia/Shanghai");
                const targetDateBJ = todayBJ.subtract(trialDays + 1, "days");
                productTargetDate = targetDateBJ.format("YYYY-MM-DD");

                console.log(`产品 ${product_id} 试用天数: ${trialDays}天，计算的试用开始日期: ${productTargetDate}`);
            }

            await this.calculateConversionRate(pk, product_id, productTargetDate);
        }
        console.log(`完成统计 ${pk} 的转正率数据`);
        
    }
    /**
     * 计算单个产品的转正率
     * @param {string} pk 包名
     * @param {string} productId 产品ID
     * @param {string} trialStartDate 试用开始日期
     */
    async calculateConversionRate(pk, productId, trialStartDate) {
        try {
            // 获取试用期天数
            let trialDays = PRODUCT_TRIAL_DAYS_MAP[productId] || DEFAULT_TRIAL_DAYS;
            if (pk == 'com.seamobitech.QRCodeReadAndGenerate' && productId == 'yearly_trial') {
                trialDays = 3
            }
            console.log(productId,'productId')
            console.log(trialDays,'trialDays')
            // 检查这个试用开始日期是否已经过了试用期
            const today = new Date().toISOString().split('T')[0];
            const trialEndDate = new Date(trialStartDate);
            trialEndDate.setDate(trialEndDate.getDate() + trialDays);
            const trialEndDateStr = trialEndDate.toISOString().split('T')[0];

            if (trialEndDateStr > today) {
                console.log(`产品 ${productId} 在 ${trialStartDate} 开始的试用还未到期，跳过转正率统计`);
                return;
            }

            // 更新试用转正跟踪表
            await this.updateTrialConversionTracking(pk, productId, trialStartDate);

            // 计算转正率
            const {
                trialToPaidCount,
                conversionRate
            } = await this.getTrialConversionStats(pk, productId, trialStartDate);

            // 更新统计表中的转正率数据
            await this.updateConversionRateStats(pk, productId, trialStartDate, trialToPaidCount, conversionRate);

            console.log(`完成产品 ${productId} 在 ${trialStartDate} 的转正率统计: ${trialToPaidCount}/${conversionRate}`);
        } catch (error) {
            console.error(`产品 ${productId} 转正率统计失败:`, error);
            throw error;
        }
    }

    /**
     * 批量执行转正率统计（处理历史数据）
     * @param {string} pk 包名
     * @param {string} startDate 开始日期
     * @param {string} endDate 结束日期
     */
    async executeHistoricalConversionStats(pk, startDate, endDate) {
        const start = new Date(startDate);
        const end = new Date(endDate);

        while (start <= end) {
            const dateStr = start.toISOString().split('T')[0];

            // 检查这个日期是否有试用数据且已过试用期
            const hasTrialData = await mysqlQueryWithPk(pk,
                `SELECT COUNT(*) as count FROM js_app_trial_conversion_tracking 
                 WHERE pk = ? AND trial_start_date = ?`,
                [pk, dateStr]
            );

            if (hasTrialData[0]?.count > 0) {
                try {
                    await this.executeConversionRateStats(pk, dateStr);
                    console.log(`完成 ${dateStr} 的转正率统计`);
                } catch (error) {
                    console.error(`${dateStr} 转正率统计失败:`, error);
                }
            } else {
                console.log(`${dateStr} 没有试用数据，跳过转正率统计`);
            }

            start.setDate(start.getDate() + 1);
        }
    }

    // 初始化试用转正跟踪表（不做转正状态更新）
    async initTrialConversionTracking(pk, productId,
        statDate) { //AND offer_discount_type = 'FREE_TRIAL' 改为 AND (offer_discount_type = 'FREE_TRIAL' OR offer_discount_type = 'PAY_AS_YOU_GO')
        // 查询所有当天新试用订单
        const rows = await mysqlQueryWithPk(pk,
            `SELECT pk, product_id, original_transaction_id, DATE(signed_date) AS trial_start_date
             FROM js_app_apple_notifications
             WHERE pk = ? AND product_id = ? AND environment = 'Production'
               AND DATE(signed_date) = ? AND subtype = 'INITIAL_BUY' AND (offer_discount_type = 'FREE_TRIAL' OR offer_discount_type = 'PAY_AS_YOU_GO')`,
            [pk, productId, statDate]
        );

        let trial_days = PRODUCT_TRIAL_DAYS_MAP[productId] || DEFAULT_TRIAL_DAYS;
        if (pk == 'com.seamobitech.QRCodeReadAndGenerate' && productId == 'yearly_trial') {
            trial_days = 3
        }
        // 插入跟踪记录
        for (const row of rows) {
            await mysqlQueryWithPk(row.pk,
                `INSERT IGNORE INTO js_app_trial_conversion_tracking
                 (id, pk, product_id, original_transaction_id, trial_start_date, trial_days, is_converted)
                 VALUES (?, ?, ?, ?, ?, ?, 0)`,
                [
                    utils.createId(),
                    row.pk,
                    row.product_id,
                    row.original_transaction_id,
                    row.trial_start_date,
                    trial_days
                ]
            );
        }
    }

    // 更新试用转正跟踪表（针对特定试用开始日期）
    async updateTrialConversionTracking(pk, productId, trialStartDate) {
        await mysqlQueryWithPk(pk,
            `UPDATE js_app_trial_conversion_tracking t
             SET t.is_converted = 1,
                 t.conversion_date = (
                     SELECT MIN(DATE(n.signed_date))
                     FROM js_app_apple_notifications n
                     WHERE n.pk = t.pk
                       AND n.product_id = t.product_id
                       AND n.original_transaction_id = t.original_transaction_id
                       AND n.environment = 'Production'
                       AND n.notification_type = 'DID_RENEW'
                       AND n.status = '1'
                       AND DATE(n.signed_date) > t.trial_start_date
                       AND DATE(n.signed_date) <= DATE_ADD(t.trial_start_date, INTERVAL t.trial_days DAY)
                 )
             WHERE t.pk = ?
               AND t.product_id = ?
               AND t.trial_start_date = ?
               AND t.is_converted = 0
               AND EXISTS (
                   SELECT 1 FROM js_app_apple_notifications n
                   WHERE n.pk = t.pk
                     AND n.product_id = t.product_id
                     AND n.original_transaction_id = t.original_transaction_id
                     AND n.environment = 'Production'
                     AND n.notification_type = 'DID_RENEW'
                     AND n.status = '1'
                     AND DATE(n.signed_date) > t.trial_start_date
                     AND DATE(n.signed_date) <= DATE_ADD(t.trial_start_date, INTERVAL t.trial_days DAY)
               )`,
            [pk, productId, trialStartDate]
        );
    }

    // 其他基础查询方法保持不变
    async getSuccessfulRenewals(pk, productId, statDate) {
        const result = await mysqlQueryWithPk(pk,
            `SELECT COUNT(*) as count
             FROM js_app_apple_notifications
             WHERE pk = ? AND product_id = ? AND environment = 'Production'
             AND DATE(signed_date) = ? AND notification_type = 'DID_RENEW' AND status = '1'`,
            [pk, productId, statDate]
        );
        return result[0]?.count || 0;
    }
    async getresubscribeCount(pk, productId, statDate) {
        const result = await mysqlQueryWithPk(pk,
            `SELECT COUNT(*) as count
             FROM js_app_apple_notifications
             WHERE pk = ? AND product_id = ? AND environment = 'Production'
             AND DATE(signed_date) = ? AND notification_type = 'SUBSCRIBED' AND subtype = 'RESUBSCRIBE' AND (offer_discount_type IS NULL OR offer_discount_type != 'FREE_TRIAL')`,
            [pk, productId, statDate]
        );
        return result[0]?.count || 0;
    }
    async getSuccessfulRefunds(pk, productId, statDate) {
        const result = await mysqlQueryWithPk(pk,
            `SELECT COUNT(*) as count
             FROM js_app_apple_notifications
             WHERE pk = ? AND product_id = ? AND environment = 'Production'
             AND DATE(signed_date) = ? AND notification_type = 'REFUND' AND revocation_date IS NOT NULL`,
            [pk, productId, statDate]
        );
        return result[0]?.count || 0;
    }

    async getTrialUsers(pk, productId,
        statDate) { //AND offer_discount_type = 'FREE_TRIAL' 改为 AND (offer_discount_type = 'FREE_TRIAL' OR offer_discount_type = 'PAY_AS_YOU_GO')
        const result = await mysqlQueryWithPk(pk,
            `SELECT COUNT(*) as count
             FROM js_app_apple_notifications
             WHERE pk = ? AND product_id = ? AND environment = 'Production'
             AND DATE(signed_date) = ? AND subtype = 'INITIAL_BUY' AND (offer_discount_type = 'FREE_TRIAL' OR offer_discount_type = 'PAY_AS_YOU_GO')`,
            [pk, productId, statDate]
        );
        return result[0]?.count || 0;
    }

    async getDirectPurchaseUsers(pk, productId,
        statDate) { //这里AND (offer_discount_type IS NULL OR offer_discount_type != 'FREE_TRIAL')改为offer_discount_type IS NULL
        const result = await mysqlQueryWithPk(pk,
            `SELECT COUNT(*) as count
             FROM js_app_apple_notifications
             WHERE pk = ? AND product_id = ? AND environment = 'Production'
             AND DATE(signed_date) = ? AND subtype = 'INITIAL_BUY'
             AND offer_discount_type IS NULL`,
            [pk, productId, statDate]
        );
        return result[0]?.count || 0;
    }

    async getTrialConversionStats(pk, productId, trialStartDate) {
        const result = await mysqlQueryWithPk(pk,
            `SELECT COUNT(*) as total_trial_users, SUM(is_converted) as converted_users
             FROM js_app_trial_conversion_tracking
             WHERE pk = ? AND product_id = ? AND trial_start_date = ?`,
            [pk, productId, trialStartDate]
        );
        const totalTrialUsers = result[0]?.total_trial_users || 0;
        const convertedUsers = result[0]?.converted_users || 0;
        const conversionRate = totalTrialUsers > 0 ? (convertedUsers / totalTrialUsers) : 0;
        return {
            trialToPaidCount: convertedUsers,
            conversionRate
        };
    }

    // 插入/更新基础统计数据
    async upsertBasicDailyStats(stats) {
        const id = utils.createId();
        await mysqlQueryWithPk(stats.pk,
            `INSERT INTO js_app_subscription_daily_stats 
                (id, pk, product_id, stat_date,resubscribe_count, successful_renewals, successful_refunds,
                trial_users, direct_purchase_users, one_time_purchase_users, trial_to_paid_count, trial_conversion_rate)
            VALUES (?, ?, ?, ?, ?, ?,?, ?, ?, ?, 0, 0.0)
            ON DUPLICATE KEY UPDATE
                resubscribe_count = VALUES(resubscribe_count),
                successful_renewals=VALUES(successful_renewals),
                successful_refunds=VALUES(successful_refunds),
                trial_users=VALUES(trial_users),
                direct_purchase_users=VALUES(direct_purchase_users),
                one_time_purchase_users=VALUES(one_time_purchase_users),
                update_date=CURRENT_TIMESTAMP`,
            [
                id,
                stats.pk,
                stats.productId,
                stats.statDate,
                stats.resubscribeCount,
                stats.successfulRenewals,
                stats.successfulRefunds,
                stats.trialUsers,
                stats.directPurchaseUsers,
                stats.oneTimePurchaseUsers ?? 0 // 如果没传则补0
            ]
        );
    }
    // async upsertBasicDailyStats(stats) {
    //     const id = utils.createId();
    //     await mysqlQueryWithPk(stats.pk,
    //         `INSERT INTO js_app_subscription_daily_stats 
    //         (id, pk, product_id, stat_date, resubscribe_count)
    //     VALUES (?, ?, ?, ?, ?)
    //     ON DUPLICATE KEY UPDATE
    //         resubscribe_count = VALUES(resubscribe_count),
    //         update_date = CURRENT_TIMESTAMP
    //     `,
    //         [
    //             id,
    //             stats.pk,
    //             stats.productId,
    //             stats.statDate,
    //             stats.resubscribeCount ?? 0
    //         ]
    //     );
    // }


    // 更新转正率数据
    async updateConversionRateStats(pk, productId, statDate, trialToPaidCount, conversionRate) {
        await mysqlQueryWithPk(pk,
            `UPDATE js_app_subscription_daily_stats 
             SET trial_to_paid_count = ?, 
                 trial_conversion_rate = ?,
                 update_date = CURRENT_TIMESTAMP
             WHERE pk = ? AND product_id = ? AND stat_date = ?`,
            [trialToPaidCount, conversionRate, pk, productId, statDate]
        );
    }

    // 批量区间执行基础统计
    async executeHistoricalBasicStats(pk, startDate, endDate) {
        const start = new Date(startDate);
        const end = new Date(endDate);
        while (start <= end) {
            const dateStr = start.toISOString().split('T')[0];

            const statRows = await mysqlQueryWithPk(pk,
                `SELECT 1 FROM js_app_subscription_daily_stats WHERE pk=? AND stat_date=? LIMIT 1`,
                [pk, dateStr]
            );
            if (statRows.length > 0) {
                console.log(`【跳过】${dateStr} 已有基础统计数据`);
            } else {
                try {
                    await this.executeDailyBasicStats(pk, dateStr);
                    console.log(`完成 ${dateStr} 的历史基础数据统计`);
                } catch (error) {
                    console.error(`${dateStr} 基础统计失败:`, error);
                }
            }
            start.setDate(start.getDate() + 1);
        }

    }

    // 兼容性方法 - 完整统计（基础+转正率）
    async executeDailyStats(pk, targetDate = null) {
        await this.executeDailyBasicStats(pk, targetDate);
        // 注意：这里不执行转正率统计，因为当天的转正率数据不完整
        console.log(`注意：${targetDate || '昨天'} 的转正率需要等待试用期结束后单独统计`);
    }

    // 兼容性方法 - 历史统计
    async executeHistoricalStats(pk, startDate, endDate) {
        await this.executeHistoricalBasicStats(pk, startDate, endDate);
        console.log(`基础统计完成，转正率需要单独执行 executeHistoricalConversionStats`);
    }
}

module.exports = AppleSubscriptionStatsService;